import time
import pymysql
from config import config


def connect():
    return pymysql.connect(
        host=config.mysql_userdata['host'],
        port=config.mysql_userdata['port'],
        user=config.mysql_userdata['user'],
        passwd=config.mysql_userdata['passwd'],
        db=config.mysql_userdata['dbname'],
        charset=config.mysql_userdata['charset'])


def patch_update_users(main_id_str, user_id_str):
    conn = connect()
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL
    update_sql = 'UPDATE information_log ' \
                 'SET user_id = CASE id ' + user_id_str + ' END ' \
                                                          'WHERE id IN (' + main_id_str + ') '
    cursor.execute(update_sql)
    conn.commit()
    # 关闭数据库连接
    conn.close()


def update_user(main_id, map_id, map_index):
    user_id = str(map_id) + str(map_index)
    conn = connect()
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL
    sql = 'update information_log set user_id="' + user_id + '" where `id`=' + str(main_id)
    is_ok = cursor.execute(sql)
    conn.commit()
    # 关闭数据库连接
    conn.close()
    return is_ok


def get_users(gh_id, page=1, page_size=100):
    start = (page - 1) * page_size
    conn = connect()
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL
    if gh_id != "":
        where = 'WHERE ghid="' + str(gh_id) + '"'
        order = 'map_id,id'
    else:
        where = ''
        order = 'map_id,ghid,id'
    sql = 'select id,map_id,ghid ' \
          'from information_log ' + where + ' order by ' + order + ' LIMIT ' + str(start) + ',' + str(page_size)
    cursor.execute(sql)
    # 获取全部数据
    data = cursor.fetchall()
    # 关闭数据库连接
    conn.close()
    return data


def get_task():
    conn = connect()
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL
    sql = 'SELECT id,ghid,table_name FROM `collect_log` where `status`=1 and process_status=0 LIMIT 1'
    try:
        cursor.execute(sql)
        # 获取全部数据
        data = cursor.fetchone()
        task_id = data[0]
        gh_id = data[1]
        table_name = data[2]
    except:
        task_id = 0
        gh_id = ''
        table_name = ''

    if task_id > 0:
        try:
            time_str = time.strftime("%Y-%m-%d %H:%M:%S")
            update_sql = 'update collect_log set process_status=1,process_start_time="' + time_str + '" where id=' + str(
                task_id)
            task_ok = cursor.execute(update_sql)
            conn.commit()
        except:
            conn.rollback()
            task_ok = 0
    else:
        task_ok = 0
    # 关闭数据库连接
    conn.close()
    return task_ok, task_id, gh_id, table_name


def get_list_for_fails(head_id, last_id=0, page_size=10):
    conn = connect()
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL
    sql = 'select id,user_head_img,user_name ' \
          'from information_log ' \
          'where head_id=' + str(head_id) + ' and id>' + str(last_id) + ' ORDER BY id ASC LIMIT ' + str(page_size)
    cursor.execute(sql)
    # 获取全部数据
    data = cursor.fetchall()
    # 关闭数据库连接
    conn.close()
    return data


def get_list_for_normals(table_name, gh_id, last_id=0, page_size=10):
    conn = connect()
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL
    sql = 'select id,user_head_img,user_name ' \
          'from ' + table_name + ' ' \
          'where id>' + str(last_id) + ' and ghid="' + gh_id + '" ' \
                                                               'ORDER BY id ASC LIMIT ' + str(page_size)
    cursor.execute(sql)
    # 获取全部数据
    data = cursor.fetchall()
    # 关闭数据库连接
    conn.close()
    return data


def patch_update(data_list):
    """
    批量插入结果数据
    """
    conn = connect()
    # 创建游标
    cursor = conn.cursor()
    main_id_str = ''
    head_id_str = ''
    head_size_str = ''
    name_base64_str = ''
    map_id_str = ''
    for item in data_list:
        main_id = str(item[0])
        head_id = str(item[1])
        head_size = str(item[2])
        name_base64 = item[3]
        map_id = item[4]
        if main_id_str == '':
            main_id_str = main_id
            head_id_str = 'WHEN ' + main_id + ' THEN ' + head_id
            head_size_str = 'WHEN ' + main_id + ' THEN ' + head_size
            name_base64_str = 'WHEN ' + main_id + ' THEN "' + name_base64 + '"'
            map_id_str = 'WHEN ' + main_id + ' THEN "' + map_id + '"'
        else:
            main_id_str = main_id_str + ',' + main_id
            head_id_str = head_id_str + ' WHEN ' + main_id + ' THEN ' + head_id
            head_size_str = head_size_str + ' WHEN ' + main_id + ' THEN ' + head_size
            name_base64_str = name_base64_str + ' WHEN ' + main_id + ' THEN "' + name_base64 + '"'
            map_id_str = map_id_str + ' WHEN ' + main_id + ' THEN "' + map_id + '"'

    update_sql = 'UPDATE information_log ' \
                 'SET head_id = CASE id ' + head_id_str + ' END, ' \
                                                          'head_size = CASE id ' + head_size_str + ' END, ' \
                                                                                                   'name_base64 = CASE id ' + name_base64_str + ' END, ' \
                                                                                                                                                'map_id = CASE id ' + map_id_str + ' END ' \
                                                                                                                                                                                   'WHERE id IN (' + main_id_str + ') '
    cursor.execute(update_sql)
    conn.commit()
    conn.close()


def handle_result(task_id):
    """
    批量更新结果数据
    """
    time_str = time.strftime("%Y-%m-%d %H:%M:%S")
    task_sql = 'update collect_log set process_status=2,process_finish_time="' + time_str + '" where id=' + str(task_id)
    conn = connect()
    # 创建游标
    cursor = conn.cursor()
    try:
        cursor.execute(task_sql)
        conn.commit()
    except:
        conn.rollback()
    conn.close()
